﻿<?xml version="1.0" encoding="utf-8"?>
<Config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SSL>false</SSL>
    <Smtp>
        <SmtpServer>smtp.sohu.com</SmtpServer>
        <SmtpPort>25</SmtpPort>
        <SmtpCredential>admin-spms</SmtpCredential>
        <CredentialPassword>admin-spms</CredentialPassword>
        <MailSender>Admin@spms.com</MailSender>
    </Smtp>
    <WebTitle>XYZ 公司 Workflow Tracking System</WebTitle>
    <WebDescription><![CDATA[ Workflow Tracking System 是一个流程跟踪管理系统。]]></WebDescription>
    <WebHostName>WorkflowSystem.net</WebHostName>
      <!--DatabaseConnectionString>Data Source=.;Initial Catalog=zb_erp;Integrated Security=True</DatabaseConnectionString-->

    <!--DatabaseConnectionString>Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Code\X1PSI\X1PSI\Data\zb_erp.mdf;Integrated Security=True;User Instance=True</DatabaseConnectionString-->	

    <DatabaseConnectionString>Data Source=.\SQLEXPRESS;Initial Catalog=ZB_ERP;Integrated Security=True</DatabaseConnectionString>	
	
    <!--DatabaseConnectionString>Data Source=.\SQLEXPRESS;AttachDbFilename=d:\code\SVN_BugOnline\EnterpriseBug\BugData\EntBugData.mdf;Integrated Security=True;User Instance=True</DatabaseConnectionString-->	
	
    <DirectoryOfUpload>D:\Code\WorkflowSystem\WorkflowSystem\Data\UploadFiles\</DirectoryOfUpload>
    <DirectoryOfDatabase>D:\Code\WorkflowSystem\WorkflowSystem\Data</DirectoryOfDatabase>
    <DirectoryOfTemplate>D:\Code\WorkflowSystem\WorkflowSystem\Data\UploadFiles\Tmp</DirectoryOfTemplate>

  <SQL1>
    create view view1 as
    SELECT DISTINCT TOP 100 PERCENT dbo.jkkb29_docu.c01 AS 单据号,
    dbo.jkkb20_item.item_nam AS 项目名称,
    case when dbo.jkkb32_det_data.c02 is null then '' else
    dbo.jkkb32_det_data.c02 end AS 对象,
    dbo.jkkb32_det_data.d01 AS 日期, dbo.jkkb29_docu.dat_yd AS 预定完成日期,
    CASE WHEN dbo.jkkb1_sta_nam.sta_rgb IS NULL
    THEN 0 ELSE dbo.jkkb1_sta_nam.sta_rgb END AS 颜色,
    case when dbo.jkkb1_sta_nam.sta_no is null then 'u' else
    dbo.jkkb1_sta_nam.sta_no end as 状态,
    SUM(dbo.jkkb32_det_data.n01) AS 数量, dbo.jkkb29_docu.flw_no AS 流程号
    FROM dbo.jkkb29_docu INNER JOIN
    dbo.jkkb20_item ON dbo.jkkb29_docu.flw_no = dbo.jkkb20_item.flw_no AND
    dbo.jkkb29_docu.item_no = dbo.jkkb20_item.item_no INNER JOIN
    dbo.jkkb32_det_data ON dbo.jkkb29_docu.flw_no = dbo.jkkb32_det_data.flw_no AND
    dbo.jkkb29_docu.c01 = dbo.jkkb32_det_data.c01 LEFT OUTER JOIN
    dbo.jkkb1_sta_nam ON dbo.jkkb29_docu.sta_no = dbo.jkkb1_sta_nam.sta_no
    WHERE dbo.jkkb29_docu.flw_no='{0}' {1}
    GROUP BY dbo.jkkb29_docu.c01, dbo.jkkb29_docu.flw_no,dbo.jkkb20_item.item_nam, dbo.jkkb32_det_data.n01,
    dbo.jkkb32_det_data.c02, dbo.jkkb32_det_data.d01, dbo.jkkb29_docu.dat_yd,
    dbo.jkkb1_sta_nam.sta_rgb,dbo.jkkb1_sta_nam.sta_no
  </SQL1>
  <SQL2>
  declare @s varchar(8000)
    set @s =  ''
    select  @s = @s +','+'颜色'+'_'+流程号+'_'+项目名称+'=max(case when 项目名称='''+项目名称+''' then 状态 else null end),'+'项目_'+流程号+'_'+项目名称+'=max(case when 项目名称='''+项目名称+''' then 预定完成日期 else null end) ' from  view1 group by 流程号,项目名称
    set @s = 'create view 项目查询表 as select 单据号,日期,对象'+@s+' from  view1 group by 单据号,日期,对象 '
    exec(@s) 
    select * from 项目查询表
  </SQL2>
</Config>